﻿create PROCEDURE Sync.SP_CustomerLoanHistory_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerLoanHistory_Temp TABLE 
		(
			LoanHistoryID UniqueIdentifier,
			[LoanID] uniqueidentifier,
				[CustomerName] nvarchar(50),
				[LoanExecutionDate] datetime,
				[LoanProjectStageID] uniqueidentifier,
				[OldCustomerCode] nvarchar(50),
				[LastConsecutiveDelay] int,
				[LastDiscreteDelay] int,
				[LoanCloseDate] datetime,
				[Notes] nvarchar(MAX),
				[LoanHistoryCloseTypeID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerLoanHistory_Temp 
	   SELECT	Tbl.Col.value('LoanHistoryID[1]','uniqueidentifier') LoanHistoryID,
                Tbl.Col.value('LoanID[1]', 'uniqueidentifier') [LoanID],
Tbl.Col.value('CustomerName[1]', 'nvarchar(50)') [CustomerName],
Tbl.Col.value('LoanExecutionDate[1]', 'datetime') [LoanExecutionDate],
Tbl.Col.value('LoanProjectStageID[1]', 'uniqueidentifier') [LoanProjectStageID],
Tbl.Col.value('OldCustomerCode[1]', 'nvarchar(50)') [OldCustomerCode],
Tbl.Col.value('LastConsecutiveDelay[1]', 'int') [LastConsecutiveDelay],
Tbl.Col.value('LastDiscreteDelay[1]', 'int') [LastDiscreteDelay],
Tbl.Col.value('LoanCloseDate[1]', 'datetime') [LoanCloseDate],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
Tbl.Col.value('LoanHistoryCloseTypeID[1]', 'uniqueidentifier') [LoanHistoryCloseTypeID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerLoanHistory') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerLoanHistory] ([LoanHistoryID],[LoanID],[CustomerName],[LoanExecutionDate],[LoanProjectStageID],[OldCustomerCode],[LastConsecutiveDelay],[LastDiscreteDelay],[LoanCloseDate],[Notes],[LoanHistoryCloseTypeID])
	SELECT t.[LoanHistoryID],t.[LoanID],t.[CustomerName],t.[LoanExecutionDate],t.[LoanProjectStageID],t.[OldCustomerCode],t.[LastConsecutiveDelay],t.[LastDiscreteDelay],t.[LoanCloseDate],t.[Notes],t.[LoanHistoryCloseTypeID]
	FROM  @CustomerLoanHistory_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerLoanHistory_Tracking s
        WHERE t.LoanHistoryID = s.LoanHistoryID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerLoanHistory]
	SET [CustomerLoanHistory].[LoanID] = t.[LoanID],[CustomerLoanHistory].[CustomerName] = t.[CustomerName],[CustomerLoanHistory].[LoanExecutionDate] = t.[LoanExecutionDate],[CustomerLoanHistory].[LoanProjectStageID] = t.[LoanProjectStageID],[CustomerLoanHistory].[OldCustomerCode] = t.[OldCustomerCode],[CustomerLoanHistory].[LastConsecutiveDelay] = t.[LastConsecutiveDelay],[CustomerLoanHistory].[LastDiscreteDelay] = t.[LastDiscreteDelay],[CustomerLoanHistory].[LoanCloseDate] = t.[LoanCloseDate],[CustomerLoanHistory].[Notes] = t.[Notes],[CustomerLoanHistory].[LoanHistoryCloseTypeID] = t.[LoanHistoryCloseTypeID] 
	FROM [dbo].[CustomerLoanHistory] s JOIN @CustomerLoanHistory_Temp t 
    ON t.LoanHistoryID = s.LoanHistoryID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerLoanHistory_Tracking r
				  WHERE t.LoanHistoryID = r.LoanHistoryID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerLoanHistory]
    FROM @CustomerLoanHistory_Temp t JOIN [dbo].[CustomerLoanHistory] s
    ON t.LoanHistoryID = s.LoanHistoryID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














